package com.info.dpunified.dpgen.service;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.info.dpunified.common.exception.DPUnifiedException;
import com.info.dpunified.common.utils.StringUtils;
import com.info.dpunified.dpgen.domain.RadarDefect;

public class DefectExtractor {
	
	
	private static final String[] RADAR_DUMP_COLUMNS = new String[] {"Review Name","Review Description","Review Status","Review Code","Review Created BY","Program Code","Review Creation Date","Review Date Closed","Review Artifact Type","Review Artifact Unit","Review Cycle","Review Type","Review Location","Review Effort","Review Date","Artifact Name","Artifact Version","Review Submitted Date","Estimated Review Effort","Estimated Task Effort","Efficiency","Actual Review Effort","Actual Task Effort","Activity Type","No. Of Defects","Artifact Size","Application","Module","Artifact Location","Review Request Code","RequestType","Defect Name","Defect ID","Defect Description","Defect- Type","Defect Status","Defect- Severity","Defect- Impact","Defect- Priority","Defect Date Identified","Defect Reason Code","Defect Due Date","Defect Date Closed","Defect Created By","Defect Resolved","Defect Author","Defect Action Code","Defect- Injected Stage","Defect Injected By","Defect- Detected Stage","Defect Detected By","Defect Standard Code","Defect- Action Taken","Defect- Cause","Defect Assigned To","Closing Comments"};
	
	
	public List<RadarDefect> extractDefects(String filePath) throws DPUnifiedException
	{
		
		if(filePath == null)
		{
			filePath = "G:/0.0-UBSDP/xlsdata/2011/Jun/UBSUMETD_2011_Jun.xlsx";
			filePath = "D:/0.0-DPUnified/iomania/UBSUMETD-Jul-2011.xlsx";
		}
		
		InputStream inp = null;
		try 
		{
			inp = new FileInputStream(filePath);
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 

		return extractDefects( inp, filePath);
	}
	
	
	public List<RadarDefect> extractDefects(InputStream inp, String filePath) throws DPUnifiedException
	{
		ArrayList<RadarDefect> defectList = new ArrayList<RadarDefect>();

		String identifier = filePath;
		
		if(filePath != null && filePath.indexOf("/") > 0)
		{
			String[] fileNames = filePath.split("/");
			identifier = fileNames[fileNames.length -1];
		}
		XSSFWorkbook wb = null;
		try 
		{
			  wb = new XSSFWorkbook(inp);
		}
    	catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		catch(Exception ife)
		{
			ife.printStackTrace();
			throw new DPUnifiedException("ERROR: Radar dump with invalid file format provided. Please use xls and xlsx only");
		}
		
		if(this.validateDefectDump(wb))
		{
			Object sValue = null; 
			Date dValue = null;
			//double nValue = 0;
			//boolean bValue = false;
			Sheet sheet1 = wb.getSheetAt(0);
			boolean headerRowIgnoredFlag = false;
		    for (Row row : sheet1) 
		    {
		    	if(headerRowIgnoredFlag)
		    	{
			    	RadarDefect radarDefect = new RadarDefect(identifier);
			        for (Cell cell : row) 
			        {
			            CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
			            sValue = null; 
			            dValue = null; 
			            switch (cell.getCellType()) 
			            {
			                case Cell.CELL_TYPE_STRING:
			                	sValue = cell.getRichStringCellValue().getString();
			                    break;
			                case Cell.CELL_TYPE_NUMERIC:
			                    if (DateUtil.isCellDateFormatted(cell)) {
			        	            //System.out.println(cellRef.formatAsString());
			        	            //System.out.print(" - Date");
			                    	dValue = cell.getDateCellValue();
			                    } else {
			        	            //System.out.println(cellRef.formatAsString());
			        	            //System.out.print(" - Num");
			                    	sValue = cell.getNumericCellValue();
			                    }
			                    break;
			                case Cell.CELL_TYPE_BOOLEAN:
			                {
			                	sValue = cell.getBooleanCellValue();
		        	            //System.out.println(cellRef.formatAsString());
		        	            //System.out.print(" - Boo");
			                }
			                	break;
			                case Cell.CELL_TYPE_FORMULA:
			                {
			                	sValue = cell.getCellFormula();
		        	            //System.out.println(cellRef.formatAsString());
		        	            //System.out.print(" - Form");
			                }
			                	break;
			                default:
			                	sValue = null;
			            }
			            
			            switch (cell.getColumnIndex())
			            {
			            	
			            	case 0: radarDefect.setReviewName(StringUtils.getValue(sValue));
			            	case 1: radarDefect.setReviewDescription(StringUtils.getValue(sValue));
			            	case 2: radarDefect.setReviewStatus(StringUtils.getValue(sValue));
			            	case 3: radarDefect.setReviewCode(StringUtils.getValue(sValue));
			            	case 4: radarDefect.setReviewCreatedBy(StringUtils.getValue(sValue));
			            	case 5: radarDefect.setProgramCode(StringUtils.getValue(sValue));
			            	case 6: radarDefect.setReviewCreationDate(dValue);
			            	case 7: radarDefect.setReviewDateClosed(dValue);
			            	case 8: radarDefect.setReviewArtifactType(StringUtils.getValue(sValue));
			            	case 9: radarDefect.setReviewArtifactUnit(StringUtils.getValue(sValue));
			            	case 10: radarDefect.setReviewCycle(StringUtils.getValue(sValue));
			            	case 11: radarDefect.setReviewType(StringUtils.getValue(sValue));
			            	case 12: radarDefect.setReviewLocation(StringUtils.getValue(sValue));
			            	case 13: radarDefect.setReviewEffort(StringUtils.getValue(sValue));
			            	case 14: radarDefect.setReviewDate(dValue);
			            	case 15: radarDefect.setArtifactName(StringUtils.getValue(sValue));
			            	case 16: radarDefect.setArtifactVersion(StringUtils.getValue(sValue));
			            	case 17: radarDefect.setReviewSubmittedDate(dValue);
			            	case 18: radarDefect.setEstimatedReviewEffort(StringUtils.getValue(sValue));
			            	case 19: radarDefect.setEstimatedTaskEffort(StringUtils.getValue(sValue));
			            	case 20: radarDefect.setEfficiency(StringUtils.getValue(sValue));
			            	case 21: radarDefect.setActualReviewEffort(StringUtils.getValue(sValue));
			            	case 22: radarDefect.setActualTaskEffort(StringUtils.getValue(sValue));
			            	case 23: radarDefect.setActivityType(StringUtils.getValue(sValue));
			            	case 24: radarDefect.setNoOfDefects(StringUtils.getValue(sValue));
			            	case 25: radarDefect.setArtifactSize(StringUtils.getValue(sValue));
			            	case 26: radarDefect.setApplication(StringUtils.getValue(sValue));
			            	case 27: radarDefect.setModule(StringUtils.getValue(sValue));
			            	case 28: radarDefect.setArtifactLocation(StringUtils.getValue(sValue));
			            	case 29: radarDefect.setReviewRequestCode(StringUtils.getValue(sValue));
			            	case 30: radarDefect.setRequestType(StringUtils.getValue(sValue));
			            	case 31: radarDefect.setDefectName(StringUtils.getValue(sValue));
			            	case 32: radarDefect.setDefectID(StringUtils.getValue(sValue));
			            	case 33: radarDefect.setDefectDescription(StringUtils.getValue(sValue));
			            	case 34: radarDefect.setDefectType(StringUtils.getValue(sValue));
			            	case 35: radarDefect.setDefectStatus(StringUtils.getValue(sValue));
			            	case 36: radarDefect.setDefectSeverity(StringUtils.getValue(sValue));
			            	case 37: radarDefect.setDefectImpact(StringUtils.getValue(sValue));
			            	case 38: radarDefect.setDefectPriority(StringUtils.getValue(sValue));
			            	case 39: radarDefect.setDefectDateIdentified(dValue);
			            	case 40: radarDefect.setDefectReasonCode(StringUtils.getValue(sValue));
			            	case 41: radarDefect.setDefectDueDate(dValue);
			            	case 42: radarDefect.setDefectDateClosed(dValue);
			            	case 43: radarDefect.setDefectCreatedBy(StringUtils.getValue(sValue));
			            	case 44: radarDefect.setDefectResolved(dValue);
			            	case 45: radarDefect.setDefectAuthor(StringUtils.getValue(sValue));
			            	case 46: radarDefect.setDefectActionCode(StringUtils.getValue(sValue));
			            	case 47: radarDefect.setDefectInjectedStage(StringUtils.getValue(sValue));
			            	case 48: radarDefect.setDefectInjectedBy(StringUtils.getValue(sValue));
			            	case 49: radarDefect.setDefectDetectedStage(StringUtils.getValue(sValue));
			            	case 50: radarDefect.setDefectDetectedBy(StringUtils.getValue(sValue));
			            	case 51: radarDefect.setDefectStandardCode(StringUtils.getValue(sValue));
			            	case 52: radarDefect.setDefectActionTaken(StringUtils.getValue(sValue));
			            	case 53: radarDefect.setDefectCause(StringUtils.getValue(sValue));
			            	case 54: radarDefect.setDefectAssignedTo(StringUtils.getValue(sValue));
			            	case 55: radarDefect.setClosingComments(StringUtils.getValue(sValue));
			            	
			            }
			        }
			        defectList.add(radarDefect);
		    	}
		    	else
		    	{
		    		headerRowIgnoredFlag = true;
		    	}
		    }
		}
		else
		{
			throw new DPUnifiedException("Error: Invalud Radar Defect Dump. Please upload correct file.");
		}
	    return defectList;
	}
	
	private boolean validateDefectDump(XSSFWorkbook wb)
	{
		boolean validateFlag = true;
		XSSFSheet sheet = wb.getSheet("RADARData");
		if(sheet == null)
		{
			validateFlag = false;
		}
		else
		{
			Row row = sheet.getRow(0);
			int hCount = 0;
			for(Cell cell : row)
			{
				if(!RADAR_DUMP_COLUMNS[hCount].equalsIgnoreCase(cell.getRichStringCellValue().getString()))
				{
					validateFlag = false;
					break;
				}
				hCount++;
			}
		}

		
		/*else if(wb.getSheet("DP Brainstorm") == null)
		{
			validateFlag = false;
		}*/
		
		return validateFlag;
	}

}
